package com.zyz.bookborrowing.dao;

import com.zyz.bookborrowing.bean.Student;
import com.zyz.bookborrowing.utils.DelFlagE;
import com.zyz.bookborrowing.utils.MyDButils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements IStudentDao{

    QueryRunner queryRunner;
    String sql;

    @Override
    public List<Student> list(Student stu) {

        queryRunner = MyDButils.getQueryRunner();
        sql = "select * from t_student where is_deleted = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<Student>>() {
                @Override
                public List<Student> handle(ResultSet rs) throws SQLException {
                    List<Student> list = new ArrayList<>();
                    while (rs.next()){
                        Student stu = new Student();
                        stu.setId(rs.getInt("id"));
                        stu.setStuNum(rs.getString("stu_num"));
                        stu.setStuName(rs.getString("stu_name"));
                        stu.setPhoneNum(rs.getString("phone_num"));
                        stu.setAddress(rs.getString("address"));
                        stu.setGender(rs.getString("gender"));
                        stu.setClassId(rs.getInt("class_id"));
                        stu.setClassName(rs.getString("class_name"));
                        stu.setDepartId(rs.getInt("depart_id"));
                        stu.setDepartName(rs.getString("depart_name"));
                        stu.setIsDeleted(rs.getInt("is_deleted"));
                        list.add(stu);
                    }
                    return list;
                }
            }, DelFlagE.NO.code);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Integer saveStudent(Student stu) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "insert into t_student(stu_num, stu_name, phone_num, gender, address, class_id, class_name, depart_id, depart_name)values(?,?,?,?,?,?,?,?,?)";
        try {
            return queryRunner.update(sql, stu.getStuNum(), stu.getStuName(), stu.getPhoneNum(), stu.getGender(), stu.getAddress(), stu.getClassId(), stu.getClassName(), stu.getDepartId(), stu.getDepartName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public Integer updateStudent(Student stu) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "update t_student set stu_num = ?, stu_name = ?, phone_num = ?, gender = ?, address = ?, class_id = ?, class_name = ?, depart_id = ?, depart_name = ? where id = ?";
        try {
            return queryRunner.update(sql, stu.getStuNum(), stu.getStuName(), stu.getPhoneNum(), stu.getGender(), stu.getAddress(), stu.getClassId(), stu.getClassName(), stu.getDepartId(), stu.getDepartName(), stu.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public Integer deleteById(Integer id) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "update t_student set is_deleted = ? where id = ?";
        try {
            return queryRunner.update(sql, DelFlagE.YES.code, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public Student queryById(Integer id) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "select * from t_student where id = ? and is_deleted = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<Student>() {
                @Override
                public Student handle(ResultSet rs) throws SQLException {
                    if (rs.next()){
                        Student stu = new Student();
                        stu.setId(rs.getInt("id"));
                        stu.setStuNum(rs.getString("stu_num"));
                        stu.setStuName(rs.getString("stu_name"));
                        stu.setPhoneNum(rs.getString("phone_num"));
                        stu.setAddress(rs.getString("address"));
                        stu.setGender(rs.getString("gender"));
                        stu.setClassId(rs.getInt("class_id"));
                        stu.setClassName(rs.getString("class_name"));
                        stu.setDepartId(rs.getInt("depart_id"));
                        stu.setDepartName(rs.getString("depart_name"));
                        stu.setIsDeleted(rs.getInt("is_deleted"));
                        return stu;
                    }
                    return null;
                }
            }, id, DelFlagE.NO.code);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int queryByClsId(String classId) {
        queryRunner = MyDButils.getQueryRunner();
//        count 记录一条即可
        sql = "select count(1) from t_student where is_deleted = ? and class_id = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<Integer>() {
                @Override
                public Integer handle(ResultSet resultSet) throws SQLException {
                    resultSet.next();
                    return resultSet.getInt(1);
                }
            }, DelFlagE.NO.code, classId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Student queryByStuNum(String stuNum) {
        queryRunner = MyDButils.getQueryRunner();
        sql = "select * from t_student where is_deleted = ? and stu_num = ?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<Student>() {
                @Override
                public Student handle(ResultSet rs) throws SQLException {
                    if (rs.next()){
                        Student stu = new Student();
                        stu.setId(rs.getInt("id"));
                        stu.setStuNum(rs.getString("stu_num"));
                        stu.setStuName(rs.getString("stu_name"));
                        stu.setPhoneNum(rs.getString("phone_num"));
                        stu.setAddress(rs.getString("address"));
                        stu.setGender(rs.getString("gender"));
                        stu.setClassId(rs.getInt("class_id"));
                        stu.setClassName(rs.getString("class_name"));
                        stu.setDepartId(rs.getInt("depart_id"));
                        stu.setDepartName(rs.getString("depart_name"));
                        stu.setIsDeleted(rs.getInt("is_deleted"));
                        return stu;
                    }
                    return null;
                }
            }, DelFlagE.NO.code, stuNum);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
